package com.gack.business.dao;

import java.math.BigInteger;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.gack.business.model.StoreCameras;
import com.gack.business.model.VideoStores;
import com.gack.business.vo.CameraVO;


/**
 * 
* @ClassName: CameraDao 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author (ZhangXinYu)  
* @date 2018年7月11日 上午9:01:34 
* @version V1.0
 */
@Repository
public class CameraDao {
	@Autowired
	private EntityManager entityManager;
	
	@SuppressWarnings({ "unchecked"})
	public List<VideoStores> getNoCameraStores(String cameraId,String province,String city,String area){
		List<VideoStores> stores = null;
		String jpql = "select"
						+ " *"
					+ " from"
						+ " stores"
					+ " where"
						+ " 1=1"
						+ " and is_use <> 1"
						+ " and id not in ("
							+ "select store_id from store_cameras"
							+ " where"
								+ " is_use <> 2";
		jpql += " )";
		if(province != null && !province.equals("")){
			jpql += " and province = '"+ province +"'";
		}
		if(city != null && !city.equals("")){
			jpql += " and city = '"+ city +"'";
		}
		if(area != null && !city.equals("")){
			jpql += " and area = '"+ city +"'";
		}
		jpql += " order by createtime";
		Query query = entityManager.createNativeQuery(jpql, VideoStores.class);
		stores = query.getResultList();
		if(cameraId != null && !cameraId.equals("")){
			String jpql2 = "select * from stores where id in (select store_id from store_cameras where id = :cameraid)";
			Query query2 = entityManager.createNativeQuery(jpql2, VideoStores.class);
			query2.setParameter("cameraid", cameraId);
			List<VideoStores> stores2 = query2.getResultList();
			stores.addAll(stores2);
		}
		return stores;
	}
	
	@SuppressWarnings("unchecked")
	public List<StoreCameras> getCameras(String province, String city, String area, String storeName,String chineseNmae,Integer page,Integer size){
		List<StoreCameras> cameras = null;
		String jpql = "select"
						+ " c.*,s.store_name"
					+ " from"
						+ " store_cameras c"
					+ " left join stores s"
					+ " on c.store_id = s.id"
					+ " where 1=1"
					+ " and c.is_use <> 2";
		if(chineseNmae != null && !chineseNmae.equals("")){
			jpql += " and c.chinese_name like '%"+chineseNmae+"%'";
		}
		if(storeName != null && !storeName.equals("")){
			jpql += " and s.store_name like '%"+storeName+"%'";
		}
		if(province != null && !province.equals("")){
			jpql += " and s.province = '"+province+"'";
		}
		if(city != null && !city.equals("")){
			jpql += " and s.city = '"+city+"'";
		}
		if(area != null && !area.equals("")){
			jpql += " and s.area = '"+area+"'";
		}
						
		jpql += " order by c.create_time";
		
		Query query = entityManager.createNativeQuery(jpql , CameraVO.class);
		cameras = query.setFirstResult(page*size).setMaxResults(size).getResultList();
		return cameras;
	}
	
	public Integer getCamerasTotal(String province, String city, String area, String storeName,String chineseNmae){
		String jpql = "select"
						+ " count(c.id)"
					+ " from"
						+ " store_cameras c"
					+ " left join stores s"
					+ " on c.store_id = s.id"
					+ " where 1=1"
					+ " and c.is_use <> 2";
		if(chineseNmae != null && !chineseNmae.equals("")){
			jpql += " and c.chinese_name like '%"+chineseNmae+"%'";
		}
		if(storeName != null && !storeName.equals("")){
			jpql += " and s.store_name like '%"+storeName+"%'";
		}
		if(province != null && !province.equals("")){
			jpql += " and s.province = '"+province+"'";
		}
		if(city != null && !city.equals("")){
			jpql += " and s.city = '"+city+"'";
		}
		if(area != null && !area.equals("")){
			jpql += " and s.area = '"+area+"'";
		}
						
		jpql += " order by c.create_time";
		
		Query query = entityManager.createNativeQuery(jpql);
		BigInteger count = (BigInteger) query.getSingleResult();
		return count.intValue();
	}
}
